package it.basketstats.utility.DB;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class StatsDBHelper extends SQLiteOpenHelper {
	private static final String DB_NAME = "stats";
	private static final int DB_VERSION = 1;

	public StatsDBHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "CREATE TABLE " + StatsDBContract.Teams.TABLE_NAME + " ("
				+ StatsDBContract.Teams._ID + " INTEGER PRIMARY KEY,"
				+ StatsDBContract.Teams.COLUMN_NAME
				+ " TEXT NOT NULL UNIQUE); "

				+ "CREATE TABLE " + StatsDBContract.Players.TABLE_NAME + " ("
				+ StatsDBContract.Players._ID + " INTEGER PRIMARY KEY,"
				+ StatsDBContract.Players.COLUMN_TEAM + " INTEGER NOT NULL,"
				+ StatsDBContract.Players.COLUMN_NUMBER + " INTEGER NOT NULL,"
				+ StatsDBContract.Players.COLUMN_NAME + " TEXT,"
				+ StatsDBContract.Players.COLUMN_SURNAME + " TEXT," + "UNIQUE("
				+ StatsDBContract.Players.COLUMN_TEAM + ", "
				+ StatsDBContract.Players.COLUMN_NUMBER
				+ ") ON CONFLICT FAIL); "

				+ "CREATE TABLE " + StatsDBContract.Matches.TABLE_NAME + " ("
				+ StatsDBContract.Matches._ID + " INTEGER PRIMARY KEY,"
				+ StatsDBContract.Matches.COLUMN_TEAM + " INTEGER NOT NULL,"
				+ StatsDBContract.Matches.COLUMN_OPPONENTS + " TEXT,"
				+ StatsDBContract.Matches.COLUMN_DATE + " DATE,"
				+ StatsDBContract.Matches.COLUMN_RESULT + " TEXT); "

				+ "CREATE TABLE " + StatsDBContract.Stats.TABLE_NAME + " ("
				+ StatsDBContract.Stats._ID + " INTEGER PRIMARY KEY,"
				+ StatsDBContract.Stats.COLUMN_MATCH + " INTEGER NOT NULL,"
				+ StatsDBContract.Stats.COLUMN_PLAYER + " INTEGER NOT NULL); "

				+ "ALTER TABLE `" + StatsDBContract.Players.TABLE_NAME
				+ "` ADD FOREIGN KEY (" + StatsDBContract.Players.COLUMN_TEAM
				+ ") REFERENCES `" + StatsDBContract.Teams.TABLE_NAME + "` (`"
				+ StatsDBContract.Teams._ID + "`);" + "ALTER TABLE `"
				+ StatsDBContract.Matches.TABLE_NAME + "` ADD FOREIGN KEY ("
				+ StatsDBContract.Matches.COLUMN_TEAM + ") REFERENCES `"
				+ StatsDBContract.Teams.TABLE_NAME + "` (`"
				+ StatsDBContract.Teams._ID + "`);" + "ALTER TABLE `"
				+ StatsDBContract.Stats.TABLE_NAME + "` ADD FOREIGN KEY ("
				+ StatsDBContract.Stats.COLUMN_MATCH + ") REFERENCES `"
				+ StatsDBContract.Matches.TABLE_NAME + "` (`"
				+ StatsDBContract.Matches._ID + "`);" + "ALTER TABLE `"
				+ StatsDBContract.Stats.TABLE_NAME + "` ADD FOREIGN KEY ("
				+ StatsDBContract.Stats.COLUMN_PLAYER + ") REFERENCES `"
				+ StatsDBContract.Players.TABLE_NAME + "` (`"
				+ StatsDBContract.Players._ID + "`);";

		db.execSQL(sql);
	}

	/************
	 * old implementation **************\
	 * 
	 * @Override public void onCreate(SQLiteDatabase db) { String sql =
	 *           "CREATE TABLE `teams` (\n" +
	 *           "  `name` VARCHAR NOT NULL DEFAULT 'NULL',\n" +
	 *           "  PRIMARY KEY (`name`)\n" + ");\n" + "		\n" +
	 *           "CREATE TABLE `players` (\n" +
	 *           "  `team` VARCHAR NOT NULL DEFAULT 'NULL',\n" +
	 *           "  `number` INTEGER NOT NULL DEFAULT NULL,\n" +
	 *           "  `name` VARCHAR NULL DEFAULT NULL,\n" +
	 *           "  `surname` VARCHAR NULL DEFAULT NULL,\n" +
	 *           "  PRIMARY KEY (`team`, `number`)\n" + ");\n" + "		\n" +
	 *           "CREATE TABLE `matches` (\n" +
	 *           "  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,\n" +
	 *           "  `team` VARCHAR NOT NULL DEFAULT 'NULL',\n" +
	 *           "  `opponents` VARCHAR NULL DEFAULT NULL,\n" +
	 *           "  `date` DATE NULL DEFAULT NULL,\n" +
	 *           "  `result` VARCHAR NULL DEFAULT NULL,\n" +
	 *           "  PRIMARY KEY (`id`)\n" + ");\n" + "\n" +
	 *           "DROP TABLE IF EXISTS `stats`;\n" + "		\n" +
	 *           "CREATE TABLE `stats` (\n" +
	 *           "  `match` INTEGER NULL DEFAULT NULL,\n" +
	 *           "  `team` VARCHAR NOT NULL DEFAULT 'NULL',\n" +
	 *           "  `player` INTEGER NOT NULL DEFAULT NULL,\n" +
	 *           "  PRIMARY KEY ()\n" + ");\n" + "\n" +
	 *           "ALTER TABLE `players` ADD FOREIGN KEY (team) REFERENCES `teams` (`name`);\n"
	 *           +
	 *           "ALTER TABLE `matches` ADD FOREIGN KEY (team) REFERENCES `teams` (`name`);\n"
	 *           +
	 *           "ALTER TABLE `stats` ADD FOREIGN KEY (match) REFERENCES `matches` (`id`);\n"
	 *           +
	 *           "ALTER TABLE `stats` ADD FOREIGN KEY (team) REFERENCES `teams` (`name`);\n"
	 *           +
	 *           "ALTER TABLE `stats` ADD FOREIGN KEY (player) REFERENCES `players` (`number`);"
	 *           ;
	 * 
	 *           db.execSQL(sql); } \
	 **********************************************/

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String sql = "DROP TABLE IF EXISTS " + StatsDBContract.Teams.TABLE_NAME + "; "
				+ "DROP TABLE IF EXISTS " + StatsDBContract.Players.TABLE_NAME + "; "
				+ "DROP TABLE IF EXISTS " + StatsDBContract.Matches.TABLE_NAME + "; "
				+ "DROP TABLE IF EXISTS " + StatsDBContract.Stats.TABLE_NAME + "; ";

		db.execSQL(sql);
		onCreate(db);
	}
	
	@Override
	public void onDowngrade(SQLiteDatabase db, int newVersion, int oldVersion) {
		String sql = "DROP TABLE IF EXISTS " + StatsDBContract.Teams.TABLE_NAME + "; "
				+ "DROP TABLE IF EXISTS " + StatsDBContract.Players.TABLE_NAME + "; "
				+ "DROP TABLE IF EXISTS " + StatsDBContract.Matches.TABLE_NAME + "; "
				+ "DROP TABLE IF EXISTS " + StatsDBContract.Stats.TABLE_NAME + "; ";

		db.execSQL(sql);
		onCreate(db);
	}

}
